package com.shsh.safemanage.dao;

import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.shsh.safemanage.model.Problem;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.model.WeakReport;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.Util;
import com.shsh.safemanage.util.ValueUtil;

public class WeakDao {
	private WeakReport getWeakReportFromRS(ResultSet rs) {
		ObjectInputStream in = null;

		WeakReport report = null;
		try {
			Blob reportBlob = rs.getBlob("WEAK_REPORT");

			in = new ObjectInputStream(reportBlob.getBinaryStream());

			report = (WeakReport) in.readObject();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (Exception e) {
			}
		}
		return report;
	}

	public List<WeakReport> getDanweiWeakReportListForCorp(int year) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where YEAR=? and [REPORT_TYPE] = " + Constants.WEAK_REPORT_TYPE_DANWEI;
		Connection conn = null;
		PreparedStatement pstm = null;
		List<WeakReport> reportList = new ArrayList<WeakReport>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				reportList.add(getWeakReportFromRS(rs));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return reportList;
	}

	public List<WeakReport> getWeakReportList(int year, int month, Long depart1ID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where YEAR=? and MONTH = ? and DEPARTMENT1_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<WeakReport> reportList = new ArrayList<WeakReport>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			pstm.setLong(3, depart1ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				reportList.add(getWeakReportFromRS(rs));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return reportList;
	}

	public WeakReport getWeakReportByProblemID(Long problemID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where PROBLEM_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		WeakReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getWeakReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public boolean updateWeakReport(Long problemID, WeakReport report) {
		String sql = "update [weak_month_report] set [WEAK_REPORT] = ?  where [PROBLEM_ID]=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setObject(1, Util.getBytes(report));
			pstm.setLong(2, problemID);

			pstm.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addWeakReport(WeakReport report) {
		String sql = "insert into [weak_month_report] ([YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[EQUIP_ID] ,[USER_ID] ,[PROBLEM_ID] ,[REPORT_TYPE] ,[WEAK_REPORT]) "
				+ "VALUES(?,?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, report.getYear());
			pstm.setInt(2, report.getMonth());
			pstm.setLong(3, report.getDepart1ID());
			if (report.getEquipID() == null) {
				pstm.setNull(4, Types.INTEGER);
			} else {
				pstm.setLong(4, report.getEquipID());
			}
			if (report.getUserID() == null) {
				pstm.setNull(5, Types.INTEGER);
			} else {
				pstm.setLong(5, report.getUserID());
			}
			pstm.setLong(6, report.getProblemID());
			pstm.setInt(7, report.getReportType());
			pstm.setObject(8, Util.getBytes(report));

			pstm.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public List<Problem> getGuanlirenyuanProblemList(String dateStart, String dateEnd, User user) {
		dateStart = dateStart + " 00:00:00";
		dateEnd = dateEnd + " 23:59:59";

		String sql = "select [ID] ,[PROBLEM_TITLE] ,[PROBLEM_CONTENT] ,[PROBLEM_ZHENGGAI_CUOSHI] ,[DEPARTMENT1_ID] ,[EQUIPMENT_ID] ,[WRITE_USER_ID] ,[WRITE_TIME]  , "
				+ "[PROCESS_STATUS]  ,[CLOSE_USER_ID] ,[CLOSE_TIME]  ,[PROBLEM_TYPE] ,[GRADE]  ,[POSTCHECK_ID] ,[POSTCHECK_PATTERN_ID] ,[POSTCHECK_CONTENT_ID]  , "
				+ "[POSTCHECK_STANDARD_ID]  ,[COMPLETE_DATE] ,[ESCALATE_DEPART1_ID] ,[WRITER_USER_MANAGE_GROUP_ID] ,[WRITE_USER_DEPARTMENT1_ID]  ,"
				+ "[WRITE_USER_DEPARTMENT2_ID]  ,[PROCESSING_BY_CHUSHI] ,[IS_SOLVED] ,[IS_SANWEI] "
				+ "FROM [problem] where  (PROBLEM_TYPE is null or PROBLEM_TYPE = 1) and IS_SOLVED = 0 and WRITE_USER_ID =? "
				+ " and WRITE_TIME >= '" + dateStart + "' and WRITE_TIME <='" + dateEnd + "' " + " order by ID ASC";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, user.getId());
			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {

				Problem problem = new Problem();

				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				if (rs.getObject("PROBLEM_TYPE") != null) {
					problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				}
				problem.setPostcheckID(ValueUtil.getLong(rs.getObject("POSTCHECK_ID")));
				problem.setPostcheckPatternID(ValueUtil.getLong(rs.getObject("POSTCHECK_PATTERN_ID")));
				problem.setPostcheckContentID(ValueUtil.getLong(rs.getObject("POSTCHECK_CONTENT_ID")));
				problem.setPostcheckStandardID(ValueUtil.getLong(rs.getObject("POSTCHECK_STANDARD_ID")));
				Object gradeObj = rs.getObject("GRADE");
				if (gradeObj != null) {
					problem.setGrade((Integer) gradeObj);
				}
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setEscalateDepart1ID(ValueUtil.getLong(rs.getObject("ESCALATE_DEPART1_ID")));
				problem.setCloseUserID(ValueUtil.getLong(rs.getObject("CLOSE_USER_ID")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));
				problem.setSolved(ValueUtil.getBoolean(rs.getBoolean("IS_SOLVED")));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getYixianyuangongProblemList(String dateStart, String dateEnd, Long equipID, List<User> userList) {
		dateStart = dateStart + " 00:00:00";
		dateEnd = dateEnd + " 23:59:59";
		Long[] idArray = new Long[userList.size()];
		for (int i = 0; i < idArray.length; i++) {
			idArray[i] = userList.get(i).getId();
		}
		String inClause = Util.generateInCluse(idArray, false);

		String sql = "select [ID] ,[PROBLEM_TITLE] ,[PROBLEM_CONTENT] ,[PROBLEM_ZHENGGAI_CUOSHI] ,[DEPARTMENT1_ID] ,[EQUIPMENT_ID] ,[WRITE_USER_ID] ,[WRITE_TIME]  , "
				+ "[PROCESS_STATUS]  ,[CLOSE_USER_ID] ,[CLOSE_TIME]  ,[PROBLEM_TYPE] ,[GRADE]  ,[POSTCHECK_ID] ,[POSTCHECK_PATTERN_ID] ,[POSTCHECK_CONTENT_ID]  , "
				+ "[POSTCHECK_STANDARD_ID]  ,[COMPLETE_DATE] ,[ESCALATE_DEPART1_ID] ,[WRITER_USER_MANAGE_GROUP_ID] ,[WRITE_USER_DEPARTMENT1_ID]  ,"
				+ "[WRITE_USER_DEPARTMENT2_ID]  ,[PROCESSING_BY_CHUSHI] ,[IS_SOLVED] ,[IS_SANWEI] "
				+ "FROM [problem] where EQUIPMENT_ID = ? and (PROBLEM_TYPE is null or PROBLEM_TYPE = 1) and IS_SOLVED = 0 and WRITE_USER_ID in "
				+ inClause + " and WRITE_TIME >= '" + dateStart + "' and WRITE_TIME <='" + dateEnd + "' " + " order by ID ASC";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, equipID);
			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {

				Problem problem = new Problem();

				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				if (rs.getObject("PROBLEM_TYPE") != null) {
					problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				}
				problem.setPostcheckID(ValueUtil.getLong(rs.getObject("POSTCHECK_ID")));
				problem.setPostcheckPatternID(ValueUtil.getLong(rs.getObject("POSTCHECK_PATTERN_ID")));
				problem.setPostcheckContentID(ValueUtil.getLong(rs.getObject("POSTCHECK_CONTENT_ID")));
				problem.setPostcheckStandardID(ValueUtil.getLong(rs.getObject("POSTCHECK_STANDARD_ID")));
				Object gradeObj = rs.getObject("GRADE");
				if (gradeObj != null) {
					problem.setGrade((Integer) gradeObj);
				}
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setEscalateDepart1ID(ValueUtil.getLong(rs.getObject("ESCALATE_DEPART1_ID")));
				problem.setCloseUserID(ValueUtil.getLong(rs.getObject("CLOSE_USER_ID")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));
				problem.setSolved(ValueUtil.getBoolean(rs.getBoolean("IS_SOLVED")));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public WeakReport getGuanlirenyuanWeakReport(int year, int month, Long userID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where YEAR = ? and MONTH = ? and REPORT_TYPE=? and USER_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		WeakReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			pstm.setInt(3, Constants.WEAK_REPORT_TYPE_GUANLIRENYUAN);
			if (userID == null) {
				pstm.setNull(4, Types.INTEGER);
			} else {
				pstm.setLong(4, userID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getWeakReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public WeakReport getYixianyuangongWeakReport(int year, int month, Long equipID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where YEAR = ? and MONTH = ? and REPORT_TYPE=? and EQUIP_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		WeakReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			pstm.setInt(3, Constants.WEAK_REPORT_TYPE_YIXIANYUANGONG);
			if (equipID == null) {
				pstm.setNull(4, Types.INTEGER);
			} else {
				pstm.setLong(4, equipID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getWeakReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;

	}

	public WeakReport getDanweiWeakReport(int year, int month, Long depart1ID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] , [DEPARTMENT1_ID], [EQUIP_ID], [USER_ID], [WEAK_REPORT] FROM  [weak_month_report] "
				+ " where YEAR = ? and MONTH = ? and REPORT_TYPE=? and DEPARTMENT1_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		WeakReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			pstm.setInt(3, Constants.WEAK_REPORT_TYPE_DANWEI);
			if (depart1ID == null) {
				pstm.setNull(4, Types.INTEGER);
			} else {
				pstm.setLong(4, depart1ID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getWeakReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}
}
